package com.luych.cloud.database.comparetor.mysql;

import com.luych.cloud.database.comparetor.core.Column;
import com.luych.cloud.database.comparetor.core.Connection;
import com.luych.cloud.database.comparetor.core.Database;
import com.luych.cloud.database.comparetor.core.Table;
import com.mysql.cj.MysqlType;
import com.mysql.cj.jdbc.Driver;
import com.mysql.cj.jdbc.MysqlDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.*;
import java.util.stream.Collectors;

public class MysqlConnection implements Connection {

    private static Logger LOGGER = LoggerFactory.getLogger(MysqlConnection.class);

    private MysqlDataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public MysqlConnection(String url, String username, String password) {
        this.dataSource = DataSourceBuilder.create()
                .type(MysqlDataSource.class)
                .driverClassName(Driver.class.getName())
                .url(url).username(username).password(password)
                .build();
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    private String getType(String dataType) {
        try {
            MysqlDataType.valueOf(dataType);
        } catch (IllegalArgumentException e) {
            LOGGER.error("Unknown Data Type: " + dataType);
        }
        return dataType;
    }

    private List<Table> getTables(String database) {
        LOGGER.info("Get [" + database + "] Tables ...");
        List<Map<String, Object>> tableMaps = jdbcTemplate.queryForList(
                "SELECT TABLE_NAME, TABLE_COMMENT " +
                "FROM information_schema.TABLES " +
                "WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = '" + database + "' "
        );
        LOGGER.info("Get [" + database + "] Columns ...");
        List<Map<String, Object>> columnMaps = jdbcTemplate.queryForList(
                "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT, " +
                "       CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE " +
                "FROM information_schema.COLUMNS " +
                "WHERE TABLE_SCHEMA = '" + database + "' "
        );
        LOGGER.info("Get [" + database + "] PrimaryKeys ...");
        List<Map<String, Object>> primaryKeyMaps = jdbcTemplate.queryForList(
                "SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION " +
                "FROM information_schema.KEY_COLUMN_USAGE " +
                "WHERE TABLE_SCHEMA = '" + database + "' AND CONSTRAINT_NAME = 'PRIMARY' " +
                "ORDER BY TABLE_NAME, ORDINAL_POSITION"
        );

        List<Table> tables = new ArrayList<>();
        for (Map<String, Object> tableMap : tableMaps) {
            String tableName = Objects.toString(tableMap.get("TABLE_NAME"), "");
            String tableComment = Objects.toString(tableMap.get("TABLE_COMMENT"), "");
            List<Column> columns = new ArrayList<>();
            for (Map<String, Object> columnMap : columnMaps) {
                if (tableName.equals(Objects.toString(columnMap.get("TABLE_NAME"), ""))) {
                    String columnName = Objects.toString(columnMap.get("COLUMN_NAME"), "");
                    String columnDataType = Objects.toString(columnMap.get("DATA_TYPE"), "").toUpperCase();
                    String columnColumnType = Objects.toString(columnMap.get("COLUMN_TYPE"), "").toUpperCase();
                    String columnNullable = Objects.toString(columnMap.get("IS_NULLABLE"), "");
                    String columnComment = Objects.toString(columnMap.get("COLUMN_COMMENT"), "");
                    String columnCharLength = Objects.toString(columnMap.get("CHARACTER_MAXIMUM_LENGTH"), "0");
                    String columnNumericPrecision = Objects.toString(columnMap.get("NUMERIC_PRECISION"), "0");
                    String columnNumericScale = Objects.toString(columnMap.get("NUMERIC_SCALE"), "0");
                    if (!MysqlDataType.CHAR.name().equalsIgnoreCase(columnDataType) && !MysqlDataType.VARCHAR.name().equalsIgnoreCase(columnDataType)) {
                        columnCharLength = "0";
                    }
                    if (!MysqlDataType.FLOAT.name().equalsIgnoreCase(columnDataType) &&
                            !MysqlDataType.DOUBLE.name().equalsIgnoreCase(columnDataType) &&
                            !MysqlDataType.DEC.name().equalsIgnoreCase(columnDataType) &&
                            !MysqlDataType.DECIMAL.name().equalsIgnoreCase(columnDataType) &&
                            !MysqlDataType.NUMERIC.name().equalsIgnoreCase(columnDataType)) {
                        columnNumericPrecision = "0";
                        columnNumericScale = "0";
                    }
                    Column column = new MysqlColumn(
                            columnName, getType(columnDataType), columnColumnType,
                            Long.parseLong(columnCharLength),
                            Long.parseLong(columnNumericPrecision),
                            Long.parseLong(columnNumericScale),
                            "YES".equals(columnNullable),
                            columnComment
                    );
                    columns.add(column);
                }
            }
            List<String> primaryKeys = new ArrayList<>();
            for (Map<String, Object> primaryKeyMap : primaryKeyMaps) {
                if (tableName.equals(Objects.toString(primaryKeyMap.get("TABLE_NAME"), ""))) {
                    primaryKeys.add(Objects.toString(primaryKeyMap.get("COLUMN_NAME"), ""));
                }
            }
            Table table = new MysqlTable(tableName, tableComment, columns, primaryKeys);
            tables.add(table);
        }
        return tables;
    }

    @Override
    public Database getDatabase(String name) {
        return new MysqlDatabase(name, this.getTables(name));
    }

    @Override
    public List<Database> getDatabases() {
        LOGGER.info("Get Databases ...");
        List<String> databaseNames = getDatabaseNames();
        List<Database> databases = new ArrayList<>();
        for (String databaseName : databaseNames) {
            databases.add(new MysqlDatabase(databaseName, getTables(databaseName)));
        }
        return databases;
    }

    @Override
    public List<String> getDatabaseNames() {
        List<String> databaseNames = jdbcTemplate.queryForList("SHOW DATABASES", String.class);
        databaseNames.sort(String::compareTo);
        return databaseNames;
    }

    @Override
    public List<String> getDataTypes() {
        return Arrays.stream(MysqlType.values()).map(Enum::name).collect(Collectors.toList());
    }

    @Override
    public void execute(String database, String sql) {
        MysqlDataSource dataSource = DataSourceBuilder.create()
                .type(MysqlDataSource.class)
                .driverClassName(Driver.class.getName())
                .url(this.dataSource.getUrl() + "/" + database)
                .username(this.dataSource.getUser())
                .password(this.dataSource.getPassword())
                .build();
        new JdbcTemplate(dataSource).execute(sql);
    }
}
